﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Model;

namespace DAL
{
    public class StudentScoreDAL
    {
        private SQLHelper sqlhelper = null;
        public StudentScoreDAL()
        {
            sqlhelper = new SQLHelper();
        }

        #region 按照考号清空学生答题记录-陈晓婵-2015年12月21日
        public DataTable DelStudentRecord(StudentInfoEntity StudentInfo)
        {
            DataTable dt = new DataTable();
            string sql = "Proc_DelStudentRecord";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",StudentInfo.studentID ),
                 new SqlParameter("@CollegeID",StudentInfo.CollegeID )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.StoredProcedure);
            return dt;
        }
        #endregion

        #region 清空所有学生答题记录--周洲--2015年12月14日


        /// <summary>
        /// 清空所有学生答题记录--周洲--2015年12月14日
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable DeleteALLStudentRecord()
        {
            DataTable dt = new DataTable();
            string sql = "Proc_DeleteRecord";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.StoredProcedure);
            return dt;
        }
        #endregion

        #region 清空该学院所有学生答题记录--赵寒--22015-12-22


        /// <summary>
        /// 清空该学院所有学生答题记录--赵寒--22015-12-22
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable DeleteALLStudentRecordBc(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "Proc_DeleteCollegeRecord";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@which",studentinfo.CollegeID)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.StoredProcedure);
            return dt;
        }
        #endregion

        #region 通过学号从成绩表中选择学生的成绩信息--周洲--2015年12月14日


        /// <summary>
        /// 通过学号从成绩表中选择学生的成绩信息--周洲--2015年12月14日
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectScoreByStudentID(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select * from ScoreEntity where studentID=@studentID";
            SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@studentID",studentinfo.studentID)};
            dt = sqlhelper.ExecuteQuery(sql, para, CommandType.Text);
            return dt;
        }
        #endregion

        #region 从学生表，学院表，成绩表中联合查出学生的成绩并显示到前面---周洲---2015年11月14日20:28:42

        /// <summary>
        /// 从学生表，学院表，成绩表中联合查出学生的成绩并显示到前面---周洲---2015年11月14日20:28:42
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectStudentByMajor(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select studentID as 学号 ,studentName as 姓名,(select collegeName  from CollegeEntity where collegeID =StudentInfoEntity.collegeID)as 学院,major as 专业,(Select score from ScoreEntity where studentID =StudentInfoEntity.studentID )as 得分 from StudentInfoEntity where major=@major";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter ("@major",studentinfo.major)};
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;

        }
        #endregion


        #region 从学生表，学院表，成绩表中联合查出学生的成绩并显示到前面---周洲---2015年11月14日20:28:42

        /// <summary>
        /// 从学生表，学院表，成绩表中联合查出学生的成绩并显示到前面---周洲---2015年11月14日20:28:42
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectScoreByCollege(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select studentID as 学号 ,studentName as 姓名,(select collegeName  from CollegeEntity where collegeID =StudentInfoEntity.collegeID)as 学院,major as 专业,(Select score from ScoreEntity where studentID =StudentInfoEntity.studentID )as 得分 from StudentInfoEntity where collegeID=@collegeID";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter ("@collegeID",studentinfo.CollegeID )};
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;

        }
        #endregion


        #region 选择所有需要考试学生的专业---周洲--2015年11月16日
        /// <summary>
        /// 选择所有需要考试学生的专业---周洲--2015年11月16日
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectAllMajor()
        {
            DataTable dt = new DataTable();
            string sql = "select distinct major from StudentInfoEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 选择所有需要考试学生的学院---周洲--2015年11月16日
        /// <summary>
        /// 选择所有需要考试学生的专业---周洲--2015年11月16日
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectAllCollege()
        {
            DataTable dt = new DataTable();
            string sql = "select collegeName from CollegeEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 选择所有需要考试学生的学院ID和姓名---周洲--2015年11月16日
        /// <summary>
        /// 选择所有需要考试学生的学院ID和姓名---周洲--2015年11月16日
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectAllCollegeInfo()
        {
            DataTable dt = new DataTable();
            string sql = "select * from CollegeEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 选择对应学院下拉框的专业---周洲--2015年11月17日
        /// <summary>
        /// 选择对应学院下拉框的专业---周洲--2015年11月17日
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectMajorByCollegeID(StudentInfoEntity studentinfo)
        {

            DataTable dt = new DataTable();
            string sql = "select distinct major from StudentInfoEntity where collegeID=@collegeID";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter ("@collegeID",studentinfo.CollegeID )};
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion



        #region SelectScoreByStudentID 根据学号从数据库总分表中查出此学生的各个成绩以及总成绩 2015年12月20日15:32:07 李少然
        /// <summary>
        /// 根据学号从数据库总分表中查出此学生的各个成绩以及总成绩
        /// </summary>
        /// <param name="scoreinfo"></param>
        /// <returns></returns>
        public DataTable SelectScoreByStudentID(string scoreinfo, string startTime, string endTime)
        {
            DataTable dt = new DataTable();
            string sql = "select studentID as '学号',studentName as '学生姓名',score as '总分', SelectScore as '选择题分数',WordScore as 'word题分数',ExcelScore as 'excel题分数',IEScore as 'IE题分数',WinScore as 'windows题分数', PPTScore as 'Ppt分数', timeSpan as '时间戳',CollegeID as '学院ID',ExamModel as '模式' from ScoreEntity where studentID = @studentID and (timeSpan between @startTime and @endTime)";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter ("@studentID",scoreinfo),
                new SqlParameter ("@startTime",startTime),
                new SqlParameter ("@endTime",endTime)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region //查询一段时间内的考生各科成绩及总分 修改人：李金颖-更改方法说明-2018-12-5 10:11:40
                //根据专业传回最高成绩得分——赵寒——2015-12-22 16:54:06
        /// <summary>
        /// 查询一段时间内的考生各科成绩及总分
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectTopScoreByMajor(StudentInfoEntity studentinfo, string starttime, string endtime)
        {
            DataTable dt = new DataTable();
            //string sql = "select  studentName as 姓名, studentID as 学号,score as 总分, SelectScore as 选择题, WordScore as Word, ExcelScore as Excel,IEScore as IE, WinScore as Windows,PPTScore as PPT, timeSpan as 日期 from View_Score  where  major=@major and CollegeID = @CollegeID order by 总分 desc  ";
            string sql = "select  studentName as 姓名, studentID as 学号,score as 总分, SelectScore as 选择题, WordScore as Word, ExcelScore as Excel,IEScore as IE, WinScore as Windows,PPTScore as PPT, timeSpan as 日期 from View_Score  where  major=@major and CollegeID = @CollegeID and timeSpan between @starttime and @endtime";

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@major",studentinfo.major),
            new SqlParameter("@CollegeID",studentinfo.CollegeID),
            new SqlParameter("@starttime",starttime),
            new SqlParameter("@endtime",endtime)};
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            return dt;
        }


        #endregion

        #region 米老师班级学生三次考试成绩推送-李金颖-2019年1月1日

        #region 推送考试成绩-李金颖-2018年12月30日
        /// <summary>
        /// 根据学院、专业、时间查询考生考试成绩
        /// </summary>
        /// <returns></returns>
        public DataTable SendExamScore(StudentInfoEntity studentinfo, string starttime, string endtime)
        {
            DataTable dt = new DataTable();
            string sql = "select studentID,studentName,score,major,majorClass,grade,timeSpan,isSend,UpSpan from ExamScore where CollegeID=@CollegeID and major=@major and timeSpan between @starttime and @endtime";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@major",studentinfo.major),
            new SqlParameter("@CollegeID",studentinfo.CollegeID),
            new SqlParameter("@starttime",starttime),
            new SqlParameter("@endtime",endtime)};
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        #endregion

        #region 根据学院、专业、推送属性，查询未推送的信息

        public DataTable IsSend(StudentInfoEntity studentinfo, string starttime, string endtime)
        {
            DataTable dt = new DataTable();
            string sql = "select studentID,studentName,score,major,majorClass,grade,timeSpan,isSend,UpSpan from ExamScore where CollegeID=@CollegeID and major=@major and isSend='0' and timeSpan between @starttime and @endtime";
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@major",studentinfo.major),
            new SqlParameter("@CollegeID",studentinfo.CollegeID),
            new SqlParameter("@starttime",starttime),
            new SqlParameter("@endtime",endtime)};
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        #endregion

        #region 更新推送属性为已推送-李金颖-2019年1月1日
        public int UpSend(string stuID, string timeSpan, string update)
        {
            DataTable dt = new DataTable();
            string sql = "update ExamScore set isSend=@isSend,UpSpan=@UpSpan where studentID=@studentID and timeSpan=@timeSpan";
            SqlParameter[] paras = new SqlParameter[] {new SqlParameter("@studentID",stuID),
                                                        new SqlParameter("@timeSpan",timeSpan),
                                                        new SqlParameter("@isSend","1"),
                                                        new SqlParameter("@UpSpan",update) };
            return sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
        }
        #endregion

        #region 根据学院、专业、时间获取考试考生数据到ExamScore表-李金颖-2019年1月3日

        //查询考生考试数据，然后添加考生数据到ExamScore表

        public DataTable selScore(string collegeID, string major, string starttime, string endtime)
        {
            string sql = "select score.studentID,score.studentName,score.score,s.CollegeID,s.major,s.majorClass,s.grade,score.timeSpan from ScoreEntity as score inner join StudentInfoEntity as s on s.studentID=score.studentID  where s.CollegeID=@CollegeID and s.major=@major and timeSpan between @starttime and @endtime";
            SqlParameter[] paras = new SqlParameter[]{new SqlParameter ("@CollegeID",collegeID),
                                                        new SqlParameter("@major",major),
                                                        new SqlParameter("@starttime",starttime),
                                                        new SqlParameter("@endtime",endtime) };
            return sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
        }


        public int addExamScore(StudentInfoEntity stuInfo, string score, string timeSpan)
        {
            string sql = "insert into ExamScore(studentID,studentName,score,CollegeID,major,majorClass,grade,timeSpan,isSend)values(@studentID,@studentName,@score,@CollegeID,@major,@majorClass,@grade,@timeSpan,@isSend)";
            SqlParameter[] parasinsert = new SqlParameter[]{new SqlParameter ("@studentID",stuInfo.studentID),
            new SqlParameter("@studentName",stuInfo.studentName),new SqlParameter("@score",score),
            new SqlParameter("@CollegeID",stuInfo.CollegeID),new SqlParameter("@major",stuInfo.major),
            new SqlParameter("@majorClass",stuInfo.majorClass),
            new SqlParameter("@grade",stuInfo.grade),
            new SqlParameter("@timeSpan",timeSpan),
            new SqlParameter("@isSend","0")};
            return sqlhelper.ExecuteNonQuery(sql, parasinsert, CommandType.Text);
        }
        #endregion

        #endregion

        #region 选择学生的操作模式——陈新月——2018-03-26
        /// <summary>
        /// 选择学生的操作模式——陈新月——2018-03-26
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public DataTable SelectIsexam()
        {
            DataTable dt = new DataTable();
            // string sql = "select is_exam as 模式 from SetUp";
            string sql = "select is_upload as 上传 from SetUp";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 通过专业、学院ID选出该学生的最近一次成绩+DataTable SelectScore(StudentInfoEntity studentinfo)-刘子腾-2017年11月30日11:21:58
        /// <summary>
        /// 通过专业、学院ID选出该学生的最近一次成绩
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectScore(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select  score as 总分 from View_Score  where  major=@major and CollegeID = @CollegeID and studentID=@studentID order by timeSpan desc  ";

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@major",studentinfo.major),
            new SqlParameter("@CollegeID",studentinfo.CollegeID),
            new SqlParameter("@studentID",studentinfo.studentID)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            return dt;
        }
        #endregion
        #region 整合信息-苑仁杰-2018年12月20日13:21:58



        #region 总分排名-苑仁杰-2018年12月20日11:21:58-修改人：刘檬-添加查询条件-2018年12月20日19:38:11
        /// <summary>
        /// 总分排名
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectScoreOrder(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE score>@score and  datediff(day,timeSpan,@date)=0 and ExamModel='练习模式'   ";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@score",scoreEntity.score),
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        //查询在线人数
        public DataTable SelectScoreOrder1(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE ExamModel='练习模式' and datediff(day,timeSpan,@date)=0";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 查询各项分数排名——薛帅行——2018年12月30号10点49分
        //选择题分数排名
        public DataTable SelectScore(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE SelectScore>@score and  datediff(day,timeSpan,@date)=0 and ExamModel='练习模式'   ";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@score",scoreEntity.SelectScore),
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        //Word题分数排名
        public DataTable WordScore(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE WordScore>@score and  datediff(day,timeSpan,@date)=0 and ExamModel='练习模式'   ";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@score",scoreEntity.WordScore),
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        //Excel题分数排名
        public DataTable ExcelScore(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE ExcelScore>@score and  datediff(day,timeSpan,@date)=0 and ExamModel='练习模式'   ";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@score",scoreEntity.ExcelScore),
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        //IE题分数排名
        public DataTable IEScore(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE IEScore>@score and  datediff(day,timeSpan,@date)=0 and ExamModel='练习模式'   ";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@score",scoreEntity.IEScore),
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        //Win题分数排名
        public DataTable WinScore(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE WinScore>@score and  datediff(day,timeSpan,@date)=0 and ExamModel='练习模式'   ";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@score",scoreEntity.WinScore),
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }

        //PPT题分数排名
        public DataTable PPTScore(ScoreEntity scoreEntity)
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            DataTable dt = new DataTable();

            //白爱民修改为：win7电脑可以运行的排名成绩--2018年12月23日
            //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   "; //此代码用于win10系统
            string sql = "select * from ScoreEntity WHERE PPTScore>@score and  datediff(day,timeSpan,@date)=0 and ExamModel='练习模式'   ";  //此代码用于win7台式机

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@score",scoreEntity.PPTScore),
            new SqlParameter("@date",date)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 所有当天考试总成绩，各项成绩平均值-刘檬-2018年12月20日19:42:54
        public DataTable getScore()
        {
            //获取数据库当前时间
            //查询时间格式为yyyy-MM-dd
            string sqltime = "Select convert(varchar(10),getdate(),120)";
            DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
            string date = table.Rows[0][0].ToString();

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@date",date)
            };

            string sql = "Select count(*) as Count,AVG(cast(SelectScore as float)) as SelectAvg,AVG(cast(WordScore as float)) as WordAvg,AVG(cast(ExcelScore as float)) as ExcelAvg,AVG(cast(IEScore as float)) as IEAvg,AVG(cast(WinScore as float)) as WinAvg,AVG(cast(PPTScore as float)) as PPTAvg,AVG(cast(score as float))as ScoreAvg from ScoreEntity where ExamModel='练习模式' and timeSpan like '%[+@date+]%'";
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 查询学生此次考试成绩-刘檬-2018年12月20日22:41:26

        public DataTable SelectByStudentID(ScoreEntity scoreEntity)
        {
            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@studentID",scoreEntity.studentID)
            };
            string sql = "Select top 1 * from ScoreEntity where studentID=@studentID order by timeSpan DESC ";
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 查询总成绩排名——薛帅行——12点53分
        //public DataTable SelectByStudentID1(ScoreEntity scoreEntity)
        //{
        //    //获取数据库当前时间
        //    //查询时间格式为yyyy-MM-dd
        //    string sqltime = "Select convert(varchar(10),getdate(),120)";
        //    DataTable table = sqlhelper.ExecuteQuery(sqltime, CommandType.Text);
        //    string date = table.Rows[0][0].ToString();

        //    SqlParameter[] paras = new SqlParameter[]{
        //    new SqlParameter("@studentID",scoreEntity.studentID)
        //    };
        //    string sql = "Select count(*) from ScoreEntity where convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式' ";
        //    DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
        //    return dt;

        //    //DataTable dt = new DataTable();
        //    //string sql = "select * from ScoreEntity WHERE score>@score and  convert(varchar,timeSpan,120) like '@date%' and ExamModel='练习模式'   ";

        //    //SqlParameter[] paras = new SqlParameter[]{
        //    //new SqlParameter("@score",scoreEntity.score),
        //    //new SqlParameter("@date",date)
        //    //};
        //    //dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
        //    //return dt;
        //}
        #endregion

        #region 和自己上一次的比较-苑仁杰-2018年12月20日11:21:58
        /// <summary>
        /// 和自己上一次的比较
        /// </summary>
        /// <param name="studentinfo"></param>
        /// <returns></returns>
        public DataTable SelectScoreLast(StudentInfoEntity studentinfo)
        {
            DataTable dt = new DataTable();
            string sql = "select * from ScoreEntity where studentID=@studentID order by timeSpan";

            SqlParameter[] paras = new SqlParameter[]{
            new SqlParameter("@studentID",studentinfo.studentID)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            return dt;
        }
        #endregion

        #endregion
    }

}
